package org.medicalinstrument.impl;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.medicalinstrument.bean.*;

import org.medicalinstrument.dao.ProductDao;
import org.medicalinstrument.dao.PurchaseOrderDao;
import org.medicalinstrument.database.DataSourceUtils;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.*;

public class PurchaseOrderDaoImpl implements PurchaseOrderDao {
    QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());

    @Override
    public List<PurchaseOrderMaster> getAllPurchaseOrderList() throws Exception {
        String sql = "select * from purchaseordermaster order by orderId";
        List<PurchaseOrderMaster> list = qr.query(sql, new BeanListHandler<>(PurchaseOrderMaster.class));
        System.out.println(list);
        return list;
    }

/*    public List<PurchaseOrder> getcustomerPurchaseOrderList(String accountNo) throws Exception {
        System.out.println("impl");
        String sql = "select * from purchaseorder_view where account=" + accountNo;
        List<PurchaseOrder> list = qr.query(sql, new BeanListHandler<>(PurchaseOrder.class));
        System.out.println(list);
        return list;
    }*/

    @Override
    public List<PurchaseOrderMaster> optionSelect(String accountNo, String timein, String timeout) throws SQLException {
        String sql = "select * from purchaseordermaster  ";
        System.out.println(sql);
        List list = new ArrayList();
        if (!"".equals(accountNo)) {
            String sqla = "accountNo='" + accountNo + "'";
            list.add(sqla);
        }
        if (!"".equals(timein)) {
            String sqla = "OrderTime between'" + timein + "' and '" + timeout + "'";
            list.add(sqla);
        }
        if (list.size() > 0) {
            sql += " where 1=1 and " + String.join(" and ", list);
        }
        sql += " order by orderId ";
        System.out.println(sql);
        List<PurchaseOrderMaster> list1 = qr.query(sql, new BeanListHandler<>(PurchaseOrderMaster.class));
        System.out.println(list1);
        return list1;
    }

    @Override
    public int modifyOrder(String custPhone, String getOrConsigee, String state, String consigeeAddr, String orderId, String accountNo,String productQR) throws SQLException {
        int result = 0;
        int resultm = 0, resultd = 0;
        int resulto = 0;
        if (!custPhone.equals("")) {
            System.out.println("null");
            String sql = "update purchaseordermaster set getOrConsigee = ?,custPhone = ?,consigeeAddr = ? where orderId = ?";
            String sql1 = "update purchaseorderdetail set custPhone = ? ,orderState = ? where orderId = ? and productQR = ?";
            System.out.println(sql);
            System.out.println(sql1);
            resultm = qr.update(sql, getOrConsigee, custPhone, consigeeAddr, orderId);
            resultd = qr.update(sql1, custPhone, state, orderId, productQR);
        } else {
            System.out.println("no");
            String sql = "update purchaseordermaster set getOrConsigee = ?,consigeeAddr = ? where orderId = ?";
            String sql1 = "update purchaseorderdetail set orderState = ? where orderId = ? and productQR = ?";
            System.out.println(sql);
            System.out.println(sql1);
            resultm = qr.update(sql, getOrConsigee, consigeeAddr, orderId);
            resultd = qr.update(sql1, state, orderId, productQR);
        }
        int wfh=0,wsh=0,wfk=0,ysh=0;
        String sql3 = "select * from purchaseorderdetail where orderId = ?";
        List<PurchaseOrderDetail> list = qr.query(sql3, new BeanListHandler<>(PurchaseOrderDetail.class),orderId);
        System.out.println(list);
        for (int i = 0; i < list.size(); i++) {
            if(list.get(i).getOrderState().equals("3")){
                ysh++;
            }else if(list.get(i).getOrderState().equals("2")){
                wsh++;
            }else if(list.get(i).getOrderState().equals("1")){
                wfh++;
            }else{
                wfk++;
            }
        }
        if(ysh==list.size()){
            String sqls = "update purchaseordermaster set orderState = '3' where orderId = ?";
            System.out.println(sqls);
            resulto = qr.update(sqls,orderId);
        } else if (wfk<=list.size()&&wfk!=0) {
            String sqls = "update purchaseordermaster set orderState = '0' where orderId = ?";
            System.out.println(sqls);
            resulto = qr.update(sqls,orderId);
        } else if (wfk==0&&wfh<=list.size()&&wfh>0) {
            String sqls = "update purchaseordermaster set orderState = '1' where orderId = ?";
            System.out.println(sqls);
            resulto = qr.update(sqls,orderId);
        } else {
            String sqls = "update purchaseordermaster set orderState = '2' where orderId = ?";
            System.out.println(sqls);
            resulto = qr.update(sqls,orderId);
        }
        if (resultm > 0 && resultd > 0 && resulto > 0) {
            result = 1;
        }
        return result;
    }

    @Override
    public int delete(String orderId, String accountNo) throws SQLException {
        int result=0;
        String sql = "delete from purchaseordermaster where orderId = ?";
        String sql1 = "delete from purchaseorderdetail where orderId = ? and accountNo = ?";
        int result1 = qr.update(sql1,orderId,accountNo);
        int result2 = qr.update(sql,orderId);
        System.out.println(result1);
        System.out.println(result2);
        if(result2>0 && result1>0) {
            result = 1;
        }
        return result;
    }

    /*
    * @author cirly
    * @description : insert into main table
    * @date 2019-04-14 22:52
    * @param [conn, pm]
    * @return java.lang.Boolean
    * @modify by ：
    */
    public Boolean PurchaseMaster_add(Connection conn, PurchaseOrderMaster pm) throws Exception {
         String sql =  "insert into purchaseordermaster values(?,?,?,?,?,?,?,?,?,?)";
        int update = qr.update(sql,pm.getOrderId(),pm.getCustPhone(),pm.getAccountNo(),pm.getOrderTime(),
                pm.getConsigeeAddr(),pm.getGetOrConsigee(),pm.getBuyPrice(),
                pm.getRealPayPrice(),pm.getOrderState(),pm.getMessage());
        return update > 0;
    }
    /*
     * @author cirly
     * @description 
     * @date 2019-04-15 00:25
     * @param [conn, pd]
     * @return boolean
     * @modify by ：
     */
    public Boolean PurchasrDetail_add(Connection conn, PurchaseOrderDetail pd) throws SQLException {
        String sql = "insert into purchaseorderdetail values(?,?,?,?,?,?,?,?,?)";
        int update = qr.update(sql,pd.getCustPhone(),pd.getAccountNo(),pd.getOrderTime(),pd.getProductQR(),
                pd.getBuyTime(),pd.getBuyNumber(),pd.getPrice(),pd.getOrderID(),pd.getOrderState());
        return update > 0;

    }

    @Override
    public List<PurchaseOrder> getOrderByCustomer(String accountNo) throws SQLException {
        String sql = "select * from purchaseorder_view where accountNo = '"+ accountNo+"'";
        System.out.println(sql);
        List<PurchaseOrder> list = qr.query(sql,new BeanListHandler<>(PurchaseOrder.class));
        return list;
    }

   public Boolean updateShoppingCart(PurchaseOrderDetail pd) throws SQLException {
       String sql  = "select * from shoppingcart where productQR = " + pd.getProductQR();
       System.out.println(sql);
       //int oldCount = qr.query(sql,new ScalarHandler<>(4));
       List list =  qr.query(sql,new ColumnListHandler<Integer>(4));
       int oldCount = list.size();
       System.out.println("old:"+oldCount);
       //int oldCount = list.size();
       int buyCount = pd.getBuyNumber();
       if (buyCount == oldCount){
           sql = " delete from shoppingcart where productQR =" + pd.getProductQR();
       }else{
           sql = "update shoppingcart set buyAmount =  buyAmount-"+ buyCount + " where productQR = " + pd.getProductQR();
       }
       int update = qr.update(sql);
       ProductDao pdo = new ProductDaoImpl();
       if ((pdo.updateState(2,pd.getProductQR(),pd.getBuyNumber())&& update>0)){
           return true;
       }else{
           return false ;
       }

   }

    @Override
    public List<PurchaseOrder> getOrderDetail(String orderId, String accountNo) throws SQLException {
        System.out.println(orderId);
        String sql = " select distinct a.*,b.productName, b.productColor," +
                " b.productPhoto,c.realPayPrice,c.consigeeAddr,c.getOrConsigee from purchaseorderdetail as a join " +
                "product as b join purchaseordermaster as c where a.productQR=b.productQR " +
                "and a.orderId=c.orderId and a.orderId = ?";
        System.out.println(sql);
        List<PurchaseOrder> list = qr.query(sql,new BeanListHandler<>(PurchaseOrder.class),orderId);
        System.out.println(list);
        return list;
    }

    @Override
    public List<PurchaseOrder> optionsSelect(String name, String accountNo, String timein, String timeout) throws SQLException {
        String sql = "select distinct a.*,b.productName, b.productColor,b.productPhoto," +
                "c.realPayPrice, c.consigeeAddr,c.getOrConsigee from purchaseorderdetail as a join " +
                "product as b join purchaseordermaster as c where a.productQR=b.productQR and a.orderId=c.orderId";
        System.out.println(sql);
        List list =  new ArrayList();
        if(!"".equals(name)){
            String sqla = "productame like '%" + name +"%'";
            list.add(sqla);
        }
        if(!"".equals(accountNo)){
            String sqla = "a.accountNo='" + accountNo +"'";
            list.add(sqla);
        }
        if(!"".equals(timein)){
            String sqla = "OrderTime  between'" + timein +"' and '" + timeout + "'";
            list.add(sqla);
        }
        if(list.size()>0){
            sql += " and " + String.join(" and ",list);
        }
        sql += " order by orderId ";
        System.out.println(sql);
        List<PurchaseOrder> list1 = qr.query(sql,new BeanListHandler<>(PurchaseOrder.class));
        System.out.println(list1);
        return list1;

    }

}
